<?php

namespace app\model;

use app\utils\Md5Service;
use \think\facade\Db;
use think\facade\Log;

class CarModel
{
    public static function info($param)
    {
        return Db::table('xl_cars')
            ->where([
                ['status', '<', 3],
                ['id', '=', $param['id']],
//                 ['partner_id','=',$param['partner_id']]
            ])->find();
    }

    public static function line_data($param): array
    {
        $where[] = [
            'uid', 'like', $param['source'].'%'
        ];

        $carLists = Db::table('xl_cars')->where("status", "<", 3)
            ->where([$where])
            ->field('id,car_plate_number')
            ->select()->toArray();
        foreach ($carLists as $k => $v) {
            $condition[] = ['car_ids', 'find in set', $v['id']];
            $count = Db::table('xl_parnter_line')->where('status', '=', 1)->where($condition)->count();
            if ($count > 0) {
                //已被使用
                $carLists[$k]['type'] = 1;
            } else {
                //未被使用
                $carLists[$k]['type'] = 2;
            }
        }
        return ['carlists' => $carLists];
    }

    public static function lists($param): array
    {
        $source = PartnerUser::where('id',request()->param('uid'))->value('source');
        $where[] = [
            'cars.uid', 'like',$source."%" ,
        ];
        $where[] = [
            'cars.partner_id', '=', $param['partner_id'],
        ];
        if (isset($param['company_name']) && !empty($param['company_name'])) {
            $where[] = ['com.name', 'like', '%' . $param['company_name'] . '%'];
        } 
        if (isset($param['car_num']) && !empty($param['car_num'])) {
            $where[] = ['cars.car_plate_number', 'like', '%' . $param['car_num'] . '%'];
        }           
        if (isset($param['is_use'])) {
            if ($param['is_use'] == -1) {
                $where[] = ['cars.is_use', 'IN', [0,1]];
            }else{
                $where[] = ['cars.is_use', '=', $param['is_use']];
            }
        }               
        if ($param['is_export'] == 1) {
            $param['limit'] = Db::table('xl_cars')->alias('cars')->where($where)->count();
        }
        $carLists = Db::table('xl_cars')->alias('cars')->where("cars.status", "<", 3)
            ->join('xl_company com', 'com.id=cars.company_id', 'left')
            ->join('xl_parnter_line pl', 'pl.default_car_id=cars.id', 'left')
            ->where([$where])
            ->field('cars.*, com.name as company_name, pl.line_no')
            ->limit(($param['page'] - 1) * $param['limit'], $param['limit'])
            ->select()->toArray();

        $count = Db::table('xl_cars')->alias('cars')->where("cars.status", "<", 3)
            ->join('xl_company com', 'com.id=cars.company_id', 'left')
            ->where([$where])
            ->count();

        return ['carlists' => $carLists, 'count' => $count];
    }

    public static function conise_lists($param): array
    {
        $dataPartition = PartnerUser::where('id',request()->param('uid'))->value('source');

        $where[] = [
            'uid', 'like',$dataPartition."%",
        ];
        $carLists = Db::table('xl_cars')->where("status", "<", 3)
            ->where([$where])
            ->field('id,car_plate_number')
            ->select()->toArray();
        return $carLists;
    }

    public static function car_opdata($param): array
    {
        $where[] = [
            'partner_id', '=', $param['partner_id'],
        ];
        if (isset($param['car_plate_number'])) {
            $where[] = ['car_plate_number', 'like', '%' . $param['car_plate_number'] . '%'];
        }
        $carLists = Db::table('xl_cars')->where("status", "<", 3)
            ->where([$where])
            ->limit(($param['page'] - 1) * $param['limit'], $param['limit'])
            ->select()->toArray();
        foreach ($carLists as $k => $v) {
        }
        $count = Db::table('xl_cars')->where("status", "<", 3)
            ->where([$where])
            ->count();
        return ['carlists' => $carLists, 'count' => $count];
    }

    /**
     * 获取所有仓库类型
     * @return void
     */
    public static function GetAllWarehouseType()
    {
        return Db::table('xl_warehouse_type')->where("status", "=", 1)
            ->select();
    }

    /**
     * 获取所有业务类型
     * @return void
     */
    public static function GetAllBussineType()
    {
        return Db::table('xl_business_type')->where("status", "=", 1)
            ->select();
    }

    /**
     * 新增车辆信息
     * @return void
     */
    public static function add($data)
    {
        return Db::table('xl_cars')->insert($data);
    }

    /**
     * 编辑车辆信息
     * @return void
     */
    public static function edit($data, $id)
    {
        return Db::table('xl_cars')->where('id', '=', $id)->update($data);
    }

    /**
     * 删除车辆信息
     * @return void
     */
    public static function del($id)
    {
        return Db::table('xl_cars')->where('id', '=', $id)->update(['status' => 3]);
    }

    //车辆运维
    public static function oper($param)
    {
        $source = PartnerUser::where('id',request()->param('uid'))->value('source');
        $where[] = [
            'cars.uid', 'like',$source."%" ,
        ];
        $where[] = [
            'cars.partner_id', '=', $param['partner_id'],
        ];
        if ($param['is_export'] == 1) {
            $param['limit'] = Db::table('xl_cars')->alias('cars')->where($where)->count();
        }
        if (!empty($param['car_id'])) {
            $where[] = ['car.car_id', '=', $param['car_id']];
        }
        $sql = "SELECT
                    count(
                     IF
                     ( type = 1, id, NULL )) AS type1,
                     count(
                     IF
                     ( type = 2, id, NULL )) AS type2,
                     SUM( money ) AS money,
                     
                     SUM(
                     IF
                     ( type = 1, money, 0 )) AS type1_money,
                     SUM(
                     IF
                     ( type = 2, money, 0 )) AS type2_money,
                     SUM( fuel_amount ) AS fuel_amount,
                     car_id 
                    FROM
                     xl_bill_management 
                    WHERE
                     car_id IN ( SELECT id FROM xl_cars WHERE `status` = 1 ) 
                    GROUP BY
                 car_id";
        $countData = Db::query($sql);
        $carData = Db::table('xl_cars')->alias('cars')->where('cars.status', 1)
            ->where([$where])
            ->join('xl_company c', 'c.id=cars.company_id', 'left')
            ->field('cars.id,cars.no,cars.fuel_type,car_plate_number,cars.is_use,c.name as company_name,cars.add_time')->limit(($param['page'] - 1) * $param['limit'], $param['limit'])
            ->select()->toArray();
        $carIds = array_column($carData, 'id');
        $avgDatas = self::aveMainFee($carIds);
        $newCarData = [];
        foreach ($countData as $v) {
            $newCarData[$v['car_id']] = $v;
        }
        $diffData = self::getDiffMileage();
        foreach ($carData as $k => $v) {
            if (!empty($newCarData[$v['id']])) {
                $newCarData[$v['id']]['fuel_amount'] = round($newCarData[$v['id']]['fuel_amount'], 2);
                $carData[$k] = array_merge($v, $newCarData[$v['id']]);
            } else {
                $carData[$k]['type1'] = 0;
                $carData[$k]['type2'] = 0;
                $carData[$k]['money'] = 0;
                $carData[$k]['type1_money'] = 0;
                $carData[$k]['type2_money'] = 0;
                $carData[$k]['fuel_amount'] = 0;
                $carData[$k]['car_id'] = $v['id'];
            }
            if (!empty($diffData[$v['id']])) {
                $carData[$k]['all_mileage'] = $diffData[$v['id']];
            } else {
                $carData[$k]['all_mileage'] = '0';
            }
            if (!empty($avgDatas[$v['id']])) {
                $carData[$k]['avg_main_money'] = bcdiv($avgDatas[$v['id']], 3, 2);
            } else {
                $carData[$k]['avg_main_money'] = 0;
            }
            $carData[$k]['fule_kilom'] = self::fuleKilom($v['id']);
        }
        $count = Db::table('xl_cars')->alias('cars')->where([$where])->where('cars.status', 1)->count();
        return ['list' => $carData, 'count' => $count];
    }

    //公里油耗量
    public static function fuleKilom($carId)
    {
        $checkIsHave = Db::table('xl_bill_management')->where('car_id', $carId)->where('type', 1)->count();
        if ($checkIsHave < 3) {
            return 0;
        }
        $data = Db::table('xl_bill_management')->where('car_id', $carId)->where('type', 1)->order('id', 'desc')->limit(3)->select()->toArray();
        $diffMil = bcsub($data[0]['mileage_recorder'], $data[2]['mileage_recorder'], 2);
        $diffL = bcadd($data[1]['fuel_amount'], $data[2]['fuel_amount'], 2);
        if ($diffL == '0.00' || $diffMil == '0.00') {
            return 0;
        }
        return bcdiv($diffL, $diffMil, 2);
    }

    //平均维修费用
    public static function aveMainFee($carIds)
    {
        $threeAgoDate = date("Y-m-d ", strtotime("-3 Months"));
        $where[] = ['maintenance_time', 'between', [$threeAgoDate, date("Y-m-d", time() + 86400)]];
        $data = Db::table('xl_bill_management')->where([$where])->where('car_id', 'in', $carIds)->where('status', 1)
            ->where('type', 2)->field('car_id,sum(money) as main_money')->group('car_id')->select()->toArray();
        return array_column($data, 'main_money', 'car_id');
    }

    //计算车辆里程
    public static function getDiffMileage()
    {
        $sql = "SELECT
                    car_id,
                 IF(Max(d_mileage) >Max(u_mileage),Max(d_mileage)- MIN(u_mileage),Max(u_mileage)-MIN(u_mileage)) as diff_fig
                FROM
                xl_driver_clock
                WHERE
                 car_id in ( SELECT id FROM xl_cars WHERE `status` = 1) GROUP BY car_id";
        $result = Db::query($sql);
        return array_column($result, 'diff_fig', 'car_id');
    }
}
